<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
require_once( '../system/mdao/Class_M_OTHER.php' );
class Class_D_DNPW_AREA extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table      = "D_DNPW_AREA";
	protected $_table_shop = "D_DNPW_AREA_SHOP";
	
	protected $_path_hand_name = "大日本印刷";
	/**
	 *
	 * 周名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_tab_last_id  = "";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey   = "id";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	 protected $_order = "id";
	 
	 protected $_cols       =  array(
		"pattern_cd"     => array(AOKI_VARTYPE_STR),
		"dist_id"        => array(AOKI_VARTYPE_STR),
		"area_nums"      => array(AOKI_VARTYPE_STR),
		"hand_cd"        => array(AOKI_VARTYPE_STR),
		"center_flg"     => array(AOKI_VARTYPE_STR)
	 );
	
	 protected $_checks     =  array(
		"pattern_cd"        => array( "EXIST","NUM" ),
		"dist_name"         => array( "EXIST" ),
		"area_nums"         => array( "EXIST","NUM" ),
		"hand_name"         => array("EXIST" )
	 );
	  protected $_checksPAT =  array(
		"pattern_cd"        => array( "EXIST","NUM" ),
		"area_nums"         => array( "EXIST","NUM" )
		
	 );
	 
	 //maxlength 
	 protected $_maxlens  = array( );
	 
	function setTableNameWeek($initid = "" ){
		$this->_tab_last_id = $initid;
		$this->_table       = $this->_table.$initid;
		$this->_table_shop  = $this->_table_shop.$initid;
		
	}
	
	function getPATChecks($initid = "" ){
		return $this->_checksPAT;
		
	}
	
	function isExitsTable($intablename ="") {
		if ( empty( $intablename ) ) {
			$tabname = $this->_table;
			$sql = "show tables like '{$tabname}' ";
		} else {
			$sql = "show tables like '{$intablename}' ";
		}
		
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			return true;
		} else {
			return false;
		}
	}
	 
	 //同じ新聞の都心合計数
	 public function getcentercount( $pattern_cd ) {
		$sqldnpFrom = " FROM {$this->_table} WHERE pattern_cd = '{$pattern_cd}' and  center_flg =1 ";
	 	$sqldnp = "SELECT sum(area_nums) as c_area_nums   " .$sqldnpFrom;
	 	$this->_db->query( $sqldnp );
	 	$retdata  = array();
	 	if(  $this->_db->num_rows() ){
	 		if ( $row = $this->_db->fetch() ){
				$retdata["c_area_nums"] = empty($row["c_area_nums"]) ? 0 : $row["c_area_nums"];
			} 
	 	}else {
	 		return false;
	 	}
	 	$sql = " select sum(a.shop_per) as c_shop_per , sum(a.shop_nums) as c_shop_nums,count(*) as c_shop_count " .
	 			" from  {$this->_table_shop} a inner join " .
	 			" ( SELECT id {$sqldnpFrom} ) b on a.dnpid = b.id and a.pattern_cd = {$pattern_cd} ";
	 	
	 	$this->_db->query( $sql );
		if( $row = $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				$retdata["c_shop_per"]  = empty($row["c_shop_per"])  ? 0 : $row["c_shop_per"]; 
				$retdata["c_shop_nums"] = empty($row["c_shop_nums"]) ? 0 : $row["c_shop_nums"]; 
				$retdata["c_shop_count"] = empty($row["c_shop_count"]) ? 0 : $row["c_shop_count"]; 
			}
		}else{
			return  FALSE;
		}
		return $retdata;
	 }
	
	public function updateCenterShops($pattern, $centerSum) {
		$sql = "  update {$this->_table_shop},{$this->_table} set {$this->_table_shop}.shop_nums  = round( {$this->_table_shop}.shop_per * {$centerSum} /100)" .
				" where  {$this->_table_shop}.dnpid = {$this->_table}.id  and  {$this->_table_shop}.pattern_cd = {$this->_table}.pattern_cd " .
				" and  {$this->_table}.center_flg = '1'  and {$this->_table}.pattern_cd ={$pattern}  ";
		return $this->exec_sql($sql) ;
		 
	}
	 public function search($where, &$page = NULL,$pattern =NULL,$dist = null,$shop = null){
	 	$jointable       = " select a.dnpid,a.pattern_cd,count(*) as shop_count,sum(a.shop_nums) as shop_nums,sum(a.shop_per) as shop_per,max(a.u_time) as au_time,max(a.u_user) as au_user  from {$this->_table_shop} a ";


		//if (!empty( $shop_name )) {
		//	$jointableWhere  = " inner join M_SHOP b on a.shop_cd = b.shop_cd WHERE b.shop_name like '%{$shop_name}%' group by a.dnpid ";
		//	$fromtable = " FROM {$this->_table}  a inner  join ({$jointable} {$jointableWhere}) b on a.id = b.dnpid " ;
		//} else {
			$jointableWhere  = "  inner join M_SHOP b on a.shop_cd = b.shop_cd  ";
			if ($pattern){
				$jointableWhere = $jointableWhere." and a.pattern_cd = '{$pattern}' ";
			}
			$jointableWhere = $jointableWhere." group by a.dnpid ";
			$fromtable = " FROM {$this->_table}  a left  join ({$jointable} {$jointableWhere}) b on a.id = b.dnpid and a.pattern_cd = b.pattern_cd " ;
	//	}
	
		$fromtable = $fromtable." inner join M_HAND h on h.hand_cd = a.hand_cd ";
		if ($dist) {
			$fromtable = $fromtable." inner join M_DIST_AREA  ds on ds.dist_id = a.dist_id and  ds.dist_name like '%{$dist}%' ";
		}
	
		if ($shop) {
			$fromtable = $fromtable."  inner join (select  dnpid from {$this->_table_shop} where shop_cd = '{$shop}' ";
			if ($pattern){
				$fromtable = $fromtable." and pattern_cd = '{$pattern}' ";
			}
			$fromtable = $fromtable." group by dnpid) dnps on dnps.dnpid = a.id " ;
		}
		$sql = "SELECT a.*, h.hand_name, b.shop_count, b.shop_nums,b.shop_per,b.au_time, (select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name,(select  user_name FROM M_LOGIN where id  = b.au_user) as m_auser_name " .$fromtable;
		
		//$sqlcnt = "SELECT count(*) as cnt ".$fromtable;
		$orderby  =  "a.id";
		$pagenull =  NULL ;
		return  parent::search($where ,$pagenull ,$sql, NULL, $orderby);
	}
	
	
	 public function searchcsv($where, $pattern,$dist = null,$shop = null ){
		$jointable       = " select a.dnpid,a.pattern_cd,count(*) as ss_count,sum(a.shop_nums) as ss_nums,sum(a.shop_per) as ss_per," .
			"max(a.u_time) as au_time,max(a.u_user) as au_user  from {$this->_table_shop} a ";
		
		
		$jointableWhere  = "  inner join M_SHOP b on a.shop_cd = b.shop_cd  ";
			if ($pattern){
				$jointableWhere = $jointableWhere." and a.pattern_cd = '{$pattern}' ";
			}
			$jointableWhere = $jointableWhere." group by a.dnpid ";
		
		$titelSql = "SELECT id,groupid, verno, title, titlecell, size, map FROM M_MATRIX_VER a WHERE matrix_cd = {$this->_tab_last_id} ";
		
		$fromtable       = " FROM {$this->_table}  a left  join ({$jointable} {$jointableWhere}) b on a.id = b.dnpid and a.pattern_cd = b.pattern_cd " ;
		$fromtable       = $fromtable." inner join M_HAND h on h.hand_cd = a.hand_cd ";
		if ($dist) {
			$fromtable = $fromtable." inner join M_DIST_AREA  ds on ds.dist_id = a.dist_id and  ds.dist_name like '%{$dist}%' ";
		}
	

		if ($shop) {
			$fromtable = $fromtable."  inner join (select  dnpid from {$this->_table_shop} where shop_cd = '{$shop}' ";
			if ($pattern){
				$fromtable = $fromtable." and pattern_cd = '{$pattern}' ";
			}
			$fromtable = $fromtable." group by dnpid) dnps on dnps.dnpid = a.id " ;
		}
		$fromtable       = $fromtable."left  join ({$titelSql}) title on title.id = a.title_cd ";
		
		$sqlsum  = "SELECT a.*, h.hand_name, title.titlecell as title_name,title.map as isirgyu,title.size ," .
				" b.ss_count, b.ss_nums,b.ss_per,b.au_time, " .
				"(select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name," .
				"(select  user_name FROM M_LOGIN where id  = b.au_user) as m_auser_name " .$fromtable;
		$where = " where ".implode(' AND ', $where);
		$sqlsum= $sqlsum.$where;
		
		
		
		 $outerTable = "  SELECT mg.map_no,mg.shop_cd, mm.sort FROM   M_MAP_GROUP_SHOP  mg
				 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
				 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and  ((mm.sort=3 and mm.media_cd = {$pattern}) or (mm.sort <> 3))   ";
				 
		$sql = " SELECT  sum.* , a.shop_cd, a.shop_per, a.shop_nums,uz.user_name as zone_nm,ua.user_name as area_nm," .
				" b.shop_name,b.year_sale,c.map_no  FROM {$this->_table_shop} a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd " .
			" inner join  M_LOGIN uz on uz.id = b.zone_aja " .
			" inner join  M_LOGIN ua on ua.id = b.area_aja " .
			" inner join ({$sqlsum}) sum on sum.id = a.dnpid " .
			"  left join  ( $outerTable ) c on a.shop_cd = c.shop_cd and c.sort = sum.isirgyu ".
			" WHERE  a.pattern_cd = {$pattern} order by sum.id ";
				 
		//SELECT a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale,c.map_no FROM D_DNPW_AREA_SHOP2012010 a inner join M_SHOP b on a.shop_cd = b.shop_cd left join ( SELECT mg.map_no,mg.shop_cd FROM M_MAP_GROUP_SHOP mg INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no WHERE mg.matrix_cd = 2012010 and mg.dummy = '0' and mm.media_cd = 6 and mm.sort = 3 ) c on a.shop_cd = c.shop_cd WHERE a.dnpid = '199' and a.pattern_cd = 6 
		
		$pagenull =  NULL ;
		
		
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	/**
	 *
	 * 単一レコードを取得する
	 *
	 * @access    public
	 * @param     mixed     $id   主キーの値
	 * @return    mixed     取得に失敗した場合にFALSE, 成功した場合にレコードの内容(array)
	 */
	 /*
	public function getByAreaAdShopCd($value) {
	    $fromtable = " FROM {$this->_table_shop} " ;
		$where     = " WHERE a.dnpid = '{$value}'" ;
		
		$sql = "SELECT * " .$fromtable.$where;
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getShopCount($value) {
		$sql = "SELECT count(*)  as cnt,sum(shop_nums) as nums FROM {$this->_table_shop} WHERE dnpid = '{$value}'";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			return $this->_db->fetch();
			
		}else{
			return  null;
		}
	}*/
	//$sort '1'  => 'レギュラー',	'2'  => 'イレギュラー',	'3'  => '地域広告',	'4'  => '単店',	'5'  => '組み込み'
	
	public function getShops($value, $pattern, $sort = NULL ) {
		if ($sort) {
			if ($sort == 3){
				 $outerTable = "  SELECT mg.map_no,mg.shop_cd FROM   M_MAP_GROUP_SHOP  mg
				 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
				 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and mm.media_cd = {$pattern} and mm.sort = {$sort}  ";
			} else {
	 			 $outerTable = "  SELECT mg.map_no,mg.shop_cd FROM   M_MAP_GROUP_SHOP  mg
				 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
				 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and mm.sort = {$sort}  ";
			}
			
			$sql = " SELECT  a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale,c.map_no  FROM {$this->_table_shop} a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd " .
			"  left join  ( $outerTable ) c on a.shop_cd = c.shop_cd ".
			" WHERE a.dnpid = '{$value}' and a.pattern_cd = {$pattern}  order by a.shop_cd ";
		}else {
			$sql = " SELECT  a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale  FROM {$this->_table_shop} a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd  WHERE a.dnpid = '{$value}' and a.pattern_cd = {$pattern} order by a.shop_cd ";
		}
		
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	public function getLnewsNo($shop_cd, $pattern ) {
		$sql =  "select lnews1,lnews2,lnews3,lnews4,lnews5 from M_SHOP where shop_cd ='$shop_cd'  ";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				if ($row["lnews1"] == $pattern){
					return 1;
				}
				
				if ($row["lnews2"] == $pattern){
					return 2;
				}
				
				if ($row["lnews3"] == $pattern){
					return 3;
				}
				
				if ($row["lnews4"] == $pattern){
					return 4;
				}
				
				if ($row["lnews5"] == $pattern){
					return 5;
				}
			}
		}
		return 9;
	}
	public function getMaps( $sort, $pattern ) {
		
		if ($sort == 3){
			 $sql = "  SELECT mg.map_no,mg.shop_cd,mg.mark FROM   M_MAP_GROUP_SHOP  mg
			 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
			 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and mm.media_cd = {$pattern} and mm.sort = {$sort}  ";
		} else {
 			 $sql = "  SELECT mg.map_no,mg.shop_cd,mg.mark FROM   M_MAP_GROUP_SHOP  mg
			 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
			 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and mm.sort = {$sort}  ";
		}
		$this->_db->query( $sql );
		/*
		echo $sql;
		echo "<br>";
		*/
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function updateShopNums($area_cd, $nums) {
		$sql = "update  {$this->_table_shop} set shop_nums  = round( shop_per * {$nums} /100)   WHERE dnpid = '{$area_cd}'  " ;
		return $this->exec_sql($sql) ;
	}
	
	public function insertinto($pattern_cd ,$patternsort) {
		$loginuser = $_SESSION['ADMIN_LOGIN']['STAFF_SEQ'];
		
		//echo "*********{$patternsort}****";
		if ($patternsort == '3') {
			
			$sqlSel = " SELECT id,pattern_cd,dist_id,area_nums,hand_cd,center_flg," .
				"CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}' from D_DNP_AREA where pattern_cd = {$pattern_cd} ";
			$sql = " insert into {$this->_table}(dnpid,pattern_cd,dist_id,area_nums,hand_cd,center_flg,u_time,u_user,c_time,c_user) ";
			$sql = $sql.$sqlSel;
			
			$sqlSelShop = 	" select b.id,{$pattern_cd} as pat,shop_cd,shop_per,shop_nums," .
				" CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}' from D_DNP_AREA_SHOP a " .
				" INNER  JOIN  {$this->_table} b on a.dnpid = b.dnpid  where b.pattern_cd = {$pattern_cd} ";
			$sqlShop = 	" insert into {$this->_table_shop} (dnpid,pattern_cd,shop_cd,shop_per,shop_nums,u_time,u_user,c_time,c_user) ";
			$sqlShop = 	$sqlShop.$sqlSelShop ;
			
		}else {
			$sqlPatDel =  "delete from  D_DNPW_PATTERN  where pattern_cd = {$pattern_cd} and matrix_cd = {$this->_tab_last_id}";
			$hand_name = $this->_path_hand_name;
			$hand_cd   =  Class_M_OTHER::do_hand($hand_name);
			$sqlPat =  "insert into D_DNPW_PATTERN(matrix_cd,pattern_cd,hand_cd,hand_name,u_time,u_user,c_time,c_user) " .
				"values({$this->_tab_last_id},{$pattern_cd},{$hand_cd},'{$hand_name}',CURRENT_TIMESTAMP,'{$loginuser}',CURRENT_TIMESTAMP,'{$loginuser}')";
		}
		
		$sqlDel = "delete from  {$this->_table}  where pattern_cd = {$pattern_cd}";
		$sqlShopDel = "delete  from  {$this->_table_shop}  where pattern_cd = {$pattern_cd}  " ;

			
		$db = $this->_db;
		$this->begin_trans();
		try {
			$updateFlg = true;
			if ($patternsort == '2') {
				if ($updateFlg) {
					if (!$db->query( $sqlPatDel )) {
						$updateFlg = false;
					}
				}
				
				if ($updateFlg) {
					if (!$db->query( $sqlPat )) {
						$updateFlg = false;
					}
				}
			}
			
			
			if ($updateFlg) {
				if (!$db->query( $sqlDel )) {
					$updateFlg = false;
				}
			}
			
			if ($updateFlg) {
				if (!$db->query( $sqlShopDel )) {
					$updateFlg = false;
				}
			}
			if ($patternsort == '3') {
			
				if ($updateFlg) {
					if (!$db->query( $sql )) {
						$updateFlg = false;
					}
				}
				
				if ($updateFlg) {
					if (!$db->query( $sqlShop )) {
						$updateFlg = false;
					}
				}
			}
			
			if ( $updateFlg ){
				$this->commit_trans();
				return true;
			} else {
				$this->rollback_trans();
				return false;
			}
			
		} catch (Exception $e) {
				echo "例外キャッチ：", $e->getMessage(), "\n";
				$this->rollback_trans();
				return false;
			}
	}
	public function get_pat($pattencd){
		
	}
	public function seaarchPAT($matrix_cd, $pattencd, $shop = null){
		$innershop = "";
		if ($shop) {
			$innershop = "  inner join (select  {$pattencd} as ptd from {$this->_table_shop} where shop_cd = '{$shop}' ";
			if ($pattencd){
				$innershop = $innershop." and pattern_cd = '{$pattencd}' ";
			}
			$innershop = $innershop." group by pattern_cd) dnps on dnps.ptd = a.pattern_cd " ;
		}
		$sql =  " select a.*,p.pattern_name,b.shop_cnt,b.shop_nums ,b.ins_nums,(select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name from  D_DNPW_PATTERN a  " .
				" inner join M_PATTERN p on a.pattern_cd = p.pattern_cd" .$innershop.
				" left join  (select pattern_cd, count(*) as  shop_cnt ,sum(shop_nums) as  shop_nums,sum(ins_nums) as  ins_nums from {$this->_table_shop} where   pattern_cd = {$pattencd}) b" .
				" on a.pattern_cd = b.pattern_cd  " .
				" where  a.matrix_cd = {$matrix_cd} and  a.pattern_cd = {$pattencd} ";
		
		$this->_db->query( $sql );
		
		if( $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				return  $row;
			}
		}
		return false;
	}
	
	public function getDNPW_PATTERN($pattencd){
		$matrix_cd = $this->_tab_last_id;
		$sql =  "select a.*  from  D_DNPW_PATTERN a where a.matrix_cd = {$matrix_cd} and  a.pattern_cd = {$pattencd}  ";
		$this->_db->query( $sql );
		
		if( $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				return  $row;
			}
		}
		return false;
	}
	public function seaarchPATcsv( $pattencd ,$shop){
		
		$innershop = "";
		if ($shop) {
			$innershop = "  inner join (select  {$pattencd} as ptd from {$this->_table_shop} where shop_cd = '{$shop}' ";
			if ($pattencd){
				$innershop = $innershop." and pattern_cd = '{$pattencd}' ";
			}
			$innershop = $innershop." group by pattern_cd) dnps on dnps.ptd = a.pattern_cd " ;
		}
		$matrix_cd = $this->_tab_last_id;
		$titelSql = "SELECT id,groupid, verno, title, titlecell, size, map FROM M_MATRIX_VER a WHERE matrix_cd = {$matrix_cd} ";
		$sqlsum = " select a.id, a.u_time,a.c_time,(select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name," .
				" a.title_cd,title.titlecell as title_name,title.map as isirgyu,title.size ," .
				"a.area_nums,b.shop_cnt,b.shop_nums  as  ss_nums,b.ins_nums " .
				" from {$this->_table} a " .
				" inner join  (select dnpid,count(*) as shop_cnt, sum(shop_nums) as shop_nums , sum(ins_nums) as  ins_nums " .
				"from  {$this->_table_shop} where pattern_cd = {$pattencd} group by dnpid ) b" .
				" on a.id = b.dnpid " .$innershop.
				" left  join ({$titelSql}) title on title.id = a.title_cd ";
				
		 $outerTable = "  SELECT mg.map_no,mg.shop_cd,mm.sort  FROM   M_MAP_GROUP_SHOP  mg
				 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
				 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and " .
				 		" ((mm.media_cd = {$pattencd} and mm.sort =3) or  mm.sort <> 3)  ";
			
			$sqlShop = " SELECT sum.*, a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale,c.map_no," .
					"uz.user_name as zone_nm,ua.user_name as area_nm  FROM {$this->_table_shop} a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd " .
			
			" inner join  M_LOGIN uz on uz.id = b.zone_aja " .
			" inner join  M_LOGIN ua on ua.id = b.area_aja ".
			
			" INNER JOIN  ({$sqlsum}) sum on sum.id = a.dnpid ".
			" left join  ({$outerTable} ) c on a.shop_cd = c.shop_cd and c.sort = sum.isirgyu  " .
			
			" WHERE  a.pattern_cd = {$pattencd} order by sum.id  ";
		$sql = "SELECT  sp.*,a.area_adnums,a.ins_shop_cd,b.ins_shop_name FROM M_INS_SHOP_SHOP{$matrix_cd} a " .
				"INNER JOIN M_INS_SHOP b on a.ins_shop_cd = b.ins_shop_cd and a.area_disp_ng = '0' and a.area_pattern_cd = {$pattencd} " .
				"  inner join ({$sqlShop}) sp on sp.shop_cd = a.shop_cd " ;
		$sql = $sql. "order by sp.id,sp.shop_cd ";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getPATTitles($matrix_cd, $pattencd){
		$sql = " select a.id, a.title_cd,a.area_nums,b.shop_cnt,b.shop_nums,b.ins_nums from {$this->_table} a " .
				" inner join  (select dnpid,count(*) as shop_cnt, sum(shop_nums) as shop_nums , sum(ins_nums) as  ins_nums from  {$this->_table_shop} where pattern_cd = {$pattencd} group by dnpid ) b" .
				" on a.id = b.dnpid " ;
				
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getPattern( $pattern_cd ) {
		$sql = "SELECT media_flg FROM  M_PATTERN where pattern_cd = {$pattern_cd}";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			if ( $row = $this->_db->fetch() ){
				return  $row["media_flg"];
			}
			return 0;
		}
		
	}
	
	public function exitsPAT( $pattern_cd ) {
		$sql =  "select pattern_cd as cnt from  D_DNPW_PATTERN  where pattern_cd = {$pattern_cd} and matrix_cd = {$this->_tab_last_id}";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			
			if ( $row = $this->_db->fetch() ){
				return  true;
			}
			return true;
		}
		return false;
	}
	
		//$sort '1'  => 'レギュラー',	'2'  => 'イレギュラー',	'3'  => '地域広告',	'4'  => '単店',	'5'  => '組み込み'
	
	public function getPATShops($value, $pattern, $sort = NULL ) {
		

		//$ajatable = "select b.shop_cd, max(case u.auth when '2' then u.user_name else null end) as area ,  
		//	max(case u.auth when '3' then u.user_name else null end) as zone from M_AJAREKS b 
		//	inner join M_LOGIN u on b.userid = u.id group by b.shop_cd order by b.shop_cd";
		//	" INNER JOIN  ({$ajatable}) aja on aja.shop_cd = a.shop_cd " .
	    $inscnttable = "select shop_cd, count(*) as ins_count,sum(area_adnums)  as shop_nums from  M_INS_SHOP_SHOP{$this->_tab_last_id}" .
	    		" where  area_pattern_cd = {$pattern} and area_disp_ng ='0' group by shop_cd";
		if ($sort) {
			if ($sort == 3){
				 $outerTable = "  SELECT mg.map_no,mg.shop_cd FROM   M_MAP_GROUP_SHOP  mg
				 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
				 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and mm.media_cd = {$pattern} and mm.sort = {$sort}  ";
			} else {
	 			 $outerTable = "  SELECT mg.map_no,mg.shop_cd FROM   M_MAP_GROUP_SHOP  mg
				 INNER JOIN M_MAP_GROUP mm on mg.map_no = mm.map_no 
				 WHERE  mg.matrix_cd =  {$this->_tab_last_id}   and mg.dummy = '0' and mm.sort = {$sort}  ";
			}
			
			$sql = " SELECT  a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale,c.map_no," .
					"uz.user_name as area,ua.user_name as zone,ins.ins_count  FROM {$this->_table_shop} a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd " .
			
			" inner join  M_LOGIN uz on uz.id = b.zone_aja " .
			" inner join  M_LOGIN ua on ua.id = b.area_aja ".
			
			" INNER JOIN  ({$inscnttable}) ins on ins.shop_cd = a.shop_cd ".
			"  left join  ({$outerTable} ) c on a.shop_cd = c.shop_cd " .
			
			" WHERE a.dnpid = '{$value}' and a.pattern_cd = {$pattern} order by c.map_no,a.shop_cd ";
		}else {
			$sql = " SELECT  a.shop_cd, a.shop_per, a.shop_nums, b.shop_name,b.year_sale  FROM {$this->_table_shop} a " .
			" inner join  M_SHOP b on a.shop_cd = b.shop_cd  WHERE a.dnpid = '{$value}' and a.pattern_cd = {$pattern}  order by a.shop_cd";
		}
		
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getPATDnpid( $pattern_cd ) {
		$sql =  "select id,dnpid from  D_DNPW_PATTERN  where pattern_cd = {$pattern_cd} and matrix_cd = {$this->_tab_last_id}";
		
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){		
			if ( $row = $this->_db->fetch() ){
				$dnpid = $row["dnpid"];
				if ($dnpid < $pattern_cd * 10000){
					$dnpid = $pattern_cd * 10000 + 1;
					
				}else {
					$dnpid = $dnpid + 1;
				}
				$aid =  $row["id"];
				$sqlu =  "update D_DNPW_PATTERN set dnpid = {$dnpid}  where id = {$aid}";
				if (!$this->_db->query( $sqlu )) {
					return false;
				}
				return $dnpid;
			}
		}
		return false;
	
	}
	
	
}

?>